package com.itlong.cloud.controller.property;

import com.itlong.cloud.POJO.DTO.BaseDTO;
import com.itlong.cloud.POJO.DTO.property.*;
import com.itlong.cloud.POJO.VO.MessageVO;
import com.itlong.cloud.POJO.VO.property.*;
import com.itlong.cloud.PlatformConstants;
import com.itlong.cloud.abstracts.BaseController;
import com.itlong.cloud.annotation.AuthAccessRequired;
import com.itlong.cloud.annotation.LoggerInfo;
import com.itlong.cloud.constants.LogDescConstant;
import com.itlong.cloud.enumerate.FileCatalogEnum;
import com.itlong.cloud.enumerate.ecode.BaseErrorCodeEnum;
import com.itlong.cloud.enumerate.ecode.ErrorCode;
import com.itlong.cloud.enumerate.ecode.PropertyErrorCodeEnum;
import com.itlong.cloud.project.property.*;
import com.itlong.cloud.thrown.DataAccessException;
import com.itlong.cloud.utils.file.UploadFileUtil;
import com.itlong.cloud.utils.network.http.HttpProtocolUtil;
import com.itlong.cloud.utils.page.Page;
import com.itlong.cloud.utils.words.NumberHandlerUtil;
import com.itlong.cloud.utils.words.StringHandlerUtil;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URI;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Pattern;

/**
 * <desc>
 *      物业管理-车辆管理控制器
 * </desc>
 *
 * @createDate 2018/08/17
 */
@RestController
@RequestMapping("/project/car")
@RefreshScope
public class PropertyCarController extends BaseController {

    @Value("${" + PlatformConstants.FILE_FTP_HOST + "}")
    public String FILE_FTP_HOST;

    @Value("${" + PlatformConstants.FILE_FTP_PORT + "}")
    public String FILE_FTP_PORT;

    @Value("${" + PlatformConstants.FILE_FTP_USERNAME + "}")
    public String FILE_FTP_USERNAME;

    @Value("${" + PlatformConstants.FILE_FTP_PASSWORD + "}")
    public String FILE_FTP_PASSWORD;

    @Value("${" + PlatformConstants.FILE_ACCESS_URL + "}")
    public String FILE_ACCESS_URL;

    @Autowired
    private IPropertyCarServiceClient iPropertyCarServiceClient;

    @Autowired
    private IPropertyBuildInfoServiceClient iPropertyBuildInfoServiceClient;

    @Autowired
    private IPropertyUnitInfoServiceClient iPropertyUnitInfoServiceClient;

    @Autowired
    private IPropertyRoomInfoServiceClient iPropertyRoomInfoServiceClient;

    @Autowired
    private IPropertyParkingServiceClient iPropertyParkingServiceClient;

    /**
     * <desc>
     *      添加车辆信息
     * </desc>
     *
     * @param propertyCarSaveDTO 添加车辆信息数据DTO
     * @return 标准格式化响应结果
     * @author wangzhi
     * @createDate 2018/08/22
     */
    @ResponseBody
    @PostMapping(path = "saveCar")
    @AuthAccessRequired
    @LoggerInfo(operateDesc = LogDescConstant.CAR_SAVE_LOG_DESC)
    public Object saveCar(PropertyCarSaveDTO propertyCarSaveDTO,HttpServletRequest request){
        try {
            propertyCarSaveDTO = HttpProtocolUtil.parseRequestParamToDTO(PropertyCarSaveDTO.class, request);
            if (propertyCarSaveDTO == null || StringUtils.isBlank(propertyCarSaveDTO.getPlateNumber()) || StringUtils.isBlank(propertyCarSaveDTO.getBuildId())
                    || StringUtils.isBlank(propertyCarSaveDTO.getUnitId()) || StringUtils.isBlank(propertyCarSaveDTO.getRoomId()) || StringUtils.isBlank(propertyCarSaveDTO.getOwnerName())
                    || StringUtils.isBlank(propertyCarSaveDTO.getOwnerPhone())) {
                return new MessageVO(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
            if (StringUtils.isBlank(propertyCarSaveDTO.getProjectId())) {
                return new MessageVO(PropertyErrorCodeEnum.PUB_PROJECT_ID_EMPTY_ERR.getErrorCode());
            }
            if (propertyCarSaveDTO.getCarType() != null && !NumberHandlerUtil.isInRange(propertyCarSaveDTO.getCarType(), 1, 4)) {
                return new MessageVO(PropertyErrorCodeEnum.CAR_TYPE_AVAILABLE_ERR.getErrorCode());
            }
            propertyCarSaveDTO.setPlateNumber(propertyCarSaveDTO.getPlateNumber().toUpperCase());
            if (!propertyCarSaveDTO.getPlateNumber().matches("^[-\\u4e00-\\u9fa50-9a-zA-Z]{7,8}$")) {
                return new MessageVO(PropertyErrorCodeEnum.PROPERTY_PLATE_NUMBER_INVALID_ERR.getErrorCode());
            }
            PropertyCarGetOneDTO propertyCarGetOneDTO = new PropertyCarGetOneDTO();
            propertyCarGetOneDTO.setProjectId(propertyCarSaveDTO.getProjectId());
            propertyCarGetOneDTO.setPlateNumber(propertyCarSaveDTO.getPlateNumber());
            PropertyCarGetOneVO oneCar = iPropertyCarServiceClient.getOneCar(propertyCarGetOneDTO);
            if (oneCar != null) {
                return new MessageVO(PropertyErrorCodeEnum.PLATE_NUMBER_EXIST_ERR.getErrorCode());
            }
            PropertyCarUploadDTO propertyCarUploadDTO = new PropertyCarUploadDTO();
            boolean isMultipart = ServletFileUpload.isMultipartContent(request);
            if (isMultipart){
                MessageVO messageVO = this.uploadCarImg(request,propertyCarUploadDTO);
                if (messageVO != null){
                    return messageVO;
                }
            }
            if (StringUtils.isNotBlank(propertyCarUploadDTO.getCarPicture())) {
                propertyCarSaveDTO.setCarPicture(propertyCarUploadDTO.getCarPicture());
            }
            if (StringUtils.isNotBlank(propertyCarUploadDTO.getCarLicence())) {
                propertyCarSaveDTO.setCarLicence(propertyCarUploadDTO.getCarLicence());
            }
            if (StringUtils.isNotBlank(propertyCarUploadDTO.getOwnerLicence())) {
                propertyCarSaveDTO.setOwnerLicence(propertyCarUploadDTO.getOwnerLicence());
            }
            iPropertyCarServiceClient.saveCar(propertyCarSaveDTO);
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode());
        } catch (Exception e) {
            throw new DataAccessException("【物业-物业管理】添加车辆信息失败",e);
        }
    }

    /**
     * <desc>
     *      删除车辆，含批量删除
     * </desc>
     *
     * @param carIds 车辆id，多个用英文","相连
     * @return 标准格式化响应结果
     * @author wangzhi
     * @createDate 2018/08/23
     */
    @ResponseBody
    @PostMapping(path = "deleteCars")
    @AuthAccessRequired
    @LoggerInfo(operateDesc = LogDescConstant.CAR_DELETE_LOG_DESC)
    public Object deleteCars(String carIds){
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            carIds = request.getAttribute("carIds") == null ? null : request.getAttribute("carIds").toString();
            if (StringUtils.isBlank(carIds)) {
                return new MessageVO(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
            PropertyCarGetPageDTO propertyCarGetPageDTO = new PropertyCarGetPageDTO();
            propertyCarGetPageDTO.setCarIds(carIds);
            List<PropertyCarGetPageVO> allCars = iPropertyCarServiceClient.getAllCars(propertyCarGetPageDTO);
            for (PropertyCarGetPageVO propertyCarGetPageVO : allCars){
                if (StringUtils.isNotBlank(propertyCarGetPageVO.getEndTime())) {
                    return new MessageVO(PropertyErrorCodeEnum.PROPERTY_CAR_IN_CARD_ERR.getErrorCode());
                }
            }
            iPropertyCarServiceClient.deleteCars(carIds);
            return new MessageVO(carIds,BaseErrorCodeEnum.SUCCESS.getErrorCode());
        } catch (Exception e) {
            throw new DataAccessException("【物业-物业管理】删除车辆信息失败",e);
        }
    }

    /**
     * <desc>
     *      修改车辆
     * </desc>
     *
     * @param propertyCarUpdateDTO 修改参数DTO
     * @return 标准格式化响应结果
     * @author wangzhi
     * @createDate 2018/08/23
     */
    @ResponseBody
    @PostMapping(path = "updateCar")
    @AuthAccessRequired
    @LoggerInfo(operateDesc = LogDescConstant.CAR_UPDATE_LOG_DESC)
    public Object updateCar(PropertyCarUpdateDTO propertyCarUpdateDTO,HttpServletRequest request){
        try {
            propertyCarUpdateDTO = HttpProtocolUtil.parseRequestParamToDTO(PropertyCarUpdateDTO.class, request);
            if (propertyCarUpdateDTO == null || StringUtils.isBlank(propertyCarUpdateDTO.getBuildId()) || StringUtils.isBlank(propertyCarUpdateDTO.getUnitId())
                    || StringUtils.isBlank(propertyCarUpdateDTO.getRoomId()) || StringUtils.isBlank(propertyCarUpdateDTO.getOwnerName())
                    || StringUtils.isBlank(propertyCarUpdateDTO.getOwnerPhone())) {
                return new MessageVO(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
            if (StringUtils.isBlank(propertyCarUpdateDTO.getProjectId())) {
                return new MessageVO(PropertyErrorCodeEnum.PUB_PROJECT_ID_EMPTY_ERR.getErrorCode());
            }
            if (propertyCarUpdateDTO.getCarType() != null && !NumberHandlerUtil.isInRange(propertyCarUpdateDTO.getCarType(), 1, 4)) {
                return new MessageVO(PropertyErrorCodeEnum.CAR_TYPE_AVAILABLE_ERR.getErrorCode());
            }
            PropertyCarUploadDTO propertyCarUploadDTO = new PropertyCarUploadDTO();
            boolean isMultipart = ServletFileUpload.isMultipartContent(request);
            if (isMultipart){
                MessageVO messageVO = this.uploadCarImg(request,propertyCarUploadDTO);
                if (messageVO != null){
                    return messageVO;
                }
            }
            if (StringUtils.isNotBlank(propertyCarUploadDTO.getCarPicture())) {
                if (StringUtils.isBlank(propertyCarUpdateDTO.getCarPicture())) {
                    propertyCarUpdateDTO.setCarPicture(propertyCarUploadDTO.getCarPicture());
                } else {
                    propertyCarUpdateDTO.setCarPicture(propertyCarUpdateDTO.getCarPicture() + "," + propertyCarUploadDTO.getCarPicture());
                }
            }
            if (StringUtils.isNotBlank(propertyCarUploadDTO.getCarLicence())) {
                propertyCarUpdateDTO.setCarLicence(propertyCarUploadDTO.getCarLicence());
            }
            if (StringUtils.isNotBlank(propertyCarUploadDTO.getOwnerLicence())) {
                propertyCarUpdateDTO.setOwnerLicence(propertyCarUploadDTO.getOwnerLicence());
            }
            iPropertyCarServiceClient.updateCar(propertyCarUpdateDTO);
            return new MessageVO(propertyCarUpdateDTO.getCarId(),BaseErrorCodeEnum.SUCCESS.getErrorCode());
        } catch (Exception e) {
            throw new DataAccessException("【物业-物业管理】修改车辆信息失败",e);
        }
    }

    /**
     * <desc>
     *      下载表单导入模板
     * </desc>
     *
     * @return 标准格式化响应结果
     * @author wangzhi
     * @createDate 2018/08/23
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping("getExcelTemplate")
    public Object getExcelTemplate(){
        try {
            String templateFilePath = FILE_ACCESS_URL + FileCatalogEnum.PROJECT_STATIC_FILE.getType()
                    + PlatformConstants.CAR_IMPORT_TEMPLATE_FILE_NAME;
            URI excelTemplateDownLoadUrl = new URI(templateFilePath);
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(),(Object) excelTemplateDownLoadUrl.toString());
        } catch (Exception e) {
            throw new DataAccessException("【物业-物业管理】下载表单导入模板失败",e);
        }
    }

    /**
     * <desc>
     *      导出车辆表单
     * </desc>
     *
     * @param propertyCarGetPageDTO 查询参数
     * @return 标准格式化响应结果
     * @author wangzhi
     * @createDate 2018/08/23
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping("exportCars")
    public Object exportCars(PropertyCarGetPageDTO propertyCarGetPageDTO){
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            propertyCarGetPageDTO = HttpProtocolUtil.parseRequestParamToDTO(PropertyCarGetPageDTO.class, request);
            if (propertyCarGetPageDTO == null) {
                return new MessageVO(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
            if (StringUtils.isBlank(propertyCarGetPageDTO.getProjectId())) {
                return new MessageVO(PropertyErrorCodeEnum.PUB_PROJECT_ID_EMPTY_ERR.getErrorCode());
            }
            List<PropertyCarGetPageVO> propertyCarGetPageVOS = iPropertyCarServiceClient.getAllCars(propertyCarGetPageDTO);
            if (propertyCarGetPageVOS == null || propertyCarGetPageVOS.isEmpty()) {
                return new MessageVO(PropertyErrorCodeEnum.EXPORT_DATA_EMPTY_ERR.getErrorCode());
            }
            if (propertyCarGetPageVOS.size() > 2000) {
                return new MessageVO(PropertyErrorCodeEnum.EXPORT_DATA_TOO_MANY_ERR.getErrorCode());
            }
            String[] title = {"序号","地址","车位号","车牌","车主姓名","手机号码","车辆类型","创建时间","月卡规则","月卡截止日期"};
            short rowHeight= 420;
            //创建excel工作簿
            HSSFWorkbook workbook=new HSSFWorkbook();
            //创建工作表sheet并设置简单格式
            HSSFSheet sheet=workbook.createSheet();
            sheet.setDefaultColumnWidth(16);
            //创建第一行
            HSSFRow row=sheet.createRow(0);
            row.setHeight(rowHeight);
            HSSFCell cell=null;
            //插入第一行数据的表头
            for (int i = 0; i < title.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(title[i]);
            }
            //写入数据
            for (int carIndex = 0; carIndex < propertyCarGetPageVOS.size(); carIndex++) {
                PropertyCarGetPageVO propertyCarGetPageVO = propertyCarGetPageVOS.get(carIndex);
                int rowIndex = carIndex + 1; // 第一行已为标题行
                HSSFRow dataRow = sheet.createRow(rowIndex);
                dataRow.setHeight(rowHeight);
                HSSFCell noteCell = dataRow.createCell(0);
                noteCell.setCellValue(rowIndex);
                noteCell = dataRow.createCell(1);
                noteCell.setCellValue(propertyCarGetPageVO.getAddress());
                noteCell = dataRow.createCell(2);
                noteCell.setCellValue(propertyCarGetPageVO.getParkingNum());
                noteCell = dataRow.createCell(3);
                noteCell.setCellValue(propertyCarGetPageVO.getPlateNumber());
                noteCell = dataRow.createCell(4);
                noteCell.setCellValue(propertyCarGetPageVO.getOwnerName());
                noteCell = dataRow.createCell(5);
                noteCell.setCellValue(propertyCarGetPageVO.getOwnerPhone());
                noteCell = dataRow.createCell(6);
                if (propertyCarGetPageVO.getCarType() != null) {
                    switch (propertyCarGetPageVO.getCarType()){
                        case 1:
                            noteCell.setCellValue("微小型");
                            break;
                        case 2:
                            noteCell.setCellValue("小型");
                            break;
                        case 3:
                            noteCell.setCellValue("大型");
                            break;
                        default:
                            noteCell.setCellValue("其它");
                            break;
                    }
                } else {
                    noteCell.setCellValue("");
                }
                noteCell = dataRow.createCell(7);
                noteCell.setCellValue(propertyCarGetPageVO.getCreateTime());
                noteCell = dataRow.createCell(8);
                noteCell.setCellValue(propertyCarGetPageVO.getRuleName());
                noteCell = dataRow.createCell(9);
                noteCell.setCellValue(propertyCarGetPageVO.getEndTime());
            }
            String fileName = String.format("%s_CarInfo_%s.xls",
                    propertyCarGetPageDTO.getProjectId(),
                    DateFormatUtils.format(new Date(), "yyyyMMddHHmmssSSS"));
            fileName = URLEncoder.encode(fileName, "UTF-8");
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] content = os.toByteArray();
            InputStream exportFileStream = new ByteArrayInputStream(content);
            String exportFileUrl = this.uploadFile(exportFileStream,fileName);
            if (StringUtils.isBlank(exportFileUrl)){
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_EXPORT_FILE_SAVE_ERR.getErrorCode());
            }
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(), (Object) exportFileUrl);
        } catch (Exception e) {
            throw new DataAccessException("【物业-物业管理】导出车辆表单失败",e);
        }
    }

    /**
     * <desc>
     *      导入车辆表单
     * </desc>
     *
     * @return 标准格式化响应结果
     * @author wangzhi
     * @createDate 2018/08/23
     */
    @ResponseBody
    @PostMapping("importCars")
    @LoggerInfo(operateDesc = LogDescConstant.CAR_EXPORT_LOG_DESC)
    @AuthAccessRequired
    public Object importCars(BaseDTO baseDTO, HttpServletRequest httpServletRequest){
        try {
            baseDTO = HttpProtocolUtil.parseRequestParamToDTO(BaseDTO.class, httpServletRequest);
            if (baseDTO == null) {
                return new MessageVO(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
            if (StringUtils.isBlank(baseDTO.getProjectId())) {
                return new MessageVO(PropertyErrorCodeEnum.PUB_PROJECT_ID_EMPTY_ERR.getErrorCode());
            }
            boolean isMultipart = ServletFileUpload.isMultipartContent(httpServletRequest);
            if (!isMultipart) {
                //是否是带文件上传的表单
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_IMPORT_FILE_LOST_ERR.getErrorCode());
            }
            MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) httpServletRequest;
            Map<String, MultipartFile> fileList = multipartRequest.getFileMap();
            if (fileList.size() < 1) {
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_IMPORT_FILE_LOST_ERR.getErrorCode());
            }
            if (fileList.size() > 1) {
                //目前仅支持一次处理单个文件
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_EXCEL_FILE_TOO_MANY_ERR.getErrorCode());
            }
            List<HSSFSheet> availableSheets = new ArrayList<>(); //合法的Sheet页
            //获取Excel文件
            Map.Entry<String, MultipartFile> fileEntry = fileList.entrySet().iterator().next();
            MultipartFile excelFile = fileEntry.getValue();
            String excelFileName = excelFile.getOriginalFilename();
            // 获取上传文件的类型
            String excelContentType = StringHandlerUtil.copySubStr(excelFileName, excelFileName.lastIndexOf('.'), excelFileName.length());
            if (!(".xls".equalsIgnoreCase(excelContentType) || ".xlsx".equalsIgnoreCase(excelContentType))) {
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_IMPORT_FILE_TYPE_ERR.getErrorCode());
            }
            List<PropertyCarRowDTO> errorImportDataList;
            List<PropertyCarSaveDTO> propertyCarSaveDTOList;
            // 创建Excel操作对象，07之前版本(*.xls)使用HSSFWorkBook 07之后版本使用（*.xlsx）XSSFWorkbook
            POIFSFileSystem fileSystem = new POIFSFileSystem(excelFile.getInputStream());
            try {
                try (HSSFWorkbook workbook = new HSSFWorkbook(fileSystem)) {

                    //对导入文件的数据格式等基本信息进行合法性初步检测
                    MessageVO messageVO = this.checkImportFileValid(workbook, availableSheets);
                    if (!messageVO.getMsgCode().equals(BaseErrorCodeEnum.SUCCESS.getErrorCode().getMainCode())) {
                        return messageVO;
                    }
                    //处理Excel文件中的车辆数据
                    propertyCarSaveDTOList = new ArrayList<>();
                    errorImportDataList = this.getImportCarsFromFile(propertyCarSaveDTOList, availableSheets,baseDTO.getProjectId());
                }
            } finally {
                fileSystem.close();
            }

            if (errorImportDataList == null) {
                return new MessageVO(PropertyErrorCodeEnum.IMPORT_CAR_TOO_MANY_ERR.getErrorCode());
            }
            //若有非法数据，返回非法数据
            if (!errorImportDataList.isEmpty()) {
                return new MessageVO(PropertyErrorCodeEnum.IMPORT_FILE_INFO_INVALID_ERR.getErrorCode(), errorImportDataList);
            }
            iPropertyCarServiceClient.batchSaveCars(propertyCarSaveDTOList);

            // 等待1秒再返回前端，一定程度上缓解前端导入成功后马上查询时尚无数据的情况
            Thread.sleep(1000);
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode());
        } catch (Exception e) {
            throw new DataAccessException("【物业-物业管理】导入车辆表单失败",e);
        }
    }

    /**
     * <desc>
     *      从可用的Sheet页中解析车辆信息
     * </desc>
     *
     * @param propertyCarSaveDTOList 从Excel(*.xls)导入地址信息数据传输对象(车辆信息,用于保存到数据库)
     * @param availableSheets 合法可用到Sheet页
     * @return 错误的车位信息及其错误原因
     * @author wangzhi
     * @createDate 2018/08/23
     */
    private List<PropertyCarRowDTO> getImportCarsFromFile(List<PropertyCarSaveDTO> propertyCarSaveDTOList, List<HSSFSheet> availableSheets, String projectId) throws Exception {
        // Excel单元格数据操作对象
        DataFormatter dataFormatter = new DataFormatter();
        // 错误的行信息列表
        List<PropertyCarRowDTO> errorImportDataList = new ArrayList<>();
        int totalRowsCount = 0;
        List<String> plateNumberList = new ArrayList<>();
        // 为防止请求超时，先将车牌、车位、地址从数据库中全部查出来
        List<String> plateNumberFromDB = iPropertyCarServiceClient.getAllPlateNumberByProjectId(projectId);
        PropertyParkingGetListDTO propertyParkingGetListDTO = new PropertyParkingGetListDTO();
        propertyParkingGetListDTO.setProjectId(projectId);
        List<PropertyParkingGetVO> parkingFromDB = iPropertyParkingServiceClient.getAllParkings(propertyParkingGetListDTO);
        List<PropertyProjectRoomInfoVO> roomInfoFromDB = iPropertyRoomInfoServiceClient.getAllRoomInfoByProjectId(projectId);

        //遍历所有可用Sheet，逐行处理每个Sheet的数据
        for (HSSFSheet oneSheet : availableSheets) {
            int rowsCountOfCurrentSheet = oneSheet.getPhysicalNumberOfRows();
            totalRowsCount += rowsCountOfCurrentSheet;
            if (totalRowsCount > 501) {
                return null;
            }
            for (int rowIndex = 1; rowIndex < rowsCountOfCurrentSheet; rowIndex++) {
                HSSFRow row = oneSheet.getRow(rowIndex);
                //对当前Sheet中的当前行进行数据处理
                PropertyCarRowDTO propertyCarRowDTO = this.handleOneRowAddressInfo(row,propertyCarSaveDTOList, dataFormatter,projectId,plateNumberList,plateNumberFromDB,parkingFromDB,roomInfoFromDB);
                //若存在错误信息，则将加入错误列表
                if (!propertyCarRowDTO.getErrorCodes().isEmpty()) {
                    this.addErrorCarInfoToList(errorImportDataList,propertyCarRowDTO);
                }
            }
        }
        return errorImportDataList;
    }

    /**
     * <desc>
     *      根据Excel的一行数据获取单条"车辆信息及其错误码列表".
     * </desc>
     *
     * @param hssfRow Excel的一行数据
     * @param propertyCarSaveDTOList 从Excel(*.xls)导入地址信息数据传输对象(车辆信息,用于保存到数据库)
     * @param dataFormatter Excel单元格数据的读取器
     * @return 错误码列表
     * @author wangzhi
     * @createDate 2018/08/17
     */
    private PropertyCarRowDTO handleOneRowAddressInfo(HSSFRow hssfRow, List<PropertyCarSaveDTO> propertyCarSaveDTOList, DataFormatter dataFormatter, String projectId,List<String> plateNumberList,
                                                      List<String> plateNumberFromDB,List<PropertyParkingGetVO> parkingFromDB,List<PropertyProjectRoomInfoVO> roomInfoFromDB) throws Exception{
        PropertyCarRowDTO propertyCarRowDTO = new PropertyCarRowDTO();
        PropertyCarSaveDTO propertyCarSaveDTO = new PropertyCarSaveDTO();
        propertyCarSaveDTO.setProjectId(projectId);
        List<ErrorCode> errorCodes = propertyCarRowDTO.getErrorCodes();
        // 首先登记所属的Sheet名称和行号
        propertyCarRowDTO.setSheetName(hssfRow.getSheet().getSheetName());
        propertyCarRowDTO.setRowNum(hssfRow.getRowNum() + 1);
        // 开始处理数据
        // 处理车牌号
        String plateNumber = dataFormatter.formatCellValue(hssfRow.getCell(0));
        propertyCarRowDTO.setPlateNumber(plateNumber);
        if (StringUtils.isBlank(plateNumber)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else {
            plateNumber = plateNumber.trim().toUpperCase();
            if (plateNumberList.contains(plateNumber)) {
                errorCodes.add(PropertyErrorCodeEnum.IMPORT_CAR_EXIST_EXCEL_ERR.getErrorCode());
            }
            plateNumberList.add(plateNumber);
//            PropertyCarGetOneDTO propertyCarGetOneDTO = new PropertyCarGetOneDTO();
//            propertyCarGetOneDTO.setProjectId(projectId);
//            propertyCarGetOneDTO.setPlateNumber(plateNumber);
//            PropertyCarGetOneVO oneCar = iPropertyCarServiceClient.getOneCar(propertyCarGetOneDTO);
//            if (oneCar != null) {
//                errorCodes.add(PropertyErrorCodeEnum.PLATE_NUMBER_EXIST_ERR.getErrorCode());
//            } else {
//                propertyCarSaveDTO.setPlateNumber(plateNumber);
//            }
            if (plateNumberFromDB.contains(plateNumber)) {
                errorCodes.add(PropertyErrorCodeEnum.PLATE_NUMBER_EXIST_ERR.getErrorCode());
            }
            if (!plateNumber.matches("^[-\\u4e00-\\u9fa50-9a-zA-Z]{7,8}$")) {
                errorCodes.add(PropertyErrorCodeEnum.PROPERTY_PLATE_NUMBER_INVALID_ERR.getErrorCode());
            }
            PropertyCarGetOneDTO propertyCarGetOneDTO = new PropertyCarGetOneDTO();
            propertyCarGetOneDTO.setProjectId(projectId);
            propertyCarGetOneDTO.setPlateNumber(plateNumber);
            PropertyCarGetOneVO oneCar = iPropertyCarServiceClient.getOneCar(propertyCarGetOneDTO);
            if (oneCar != null) {
                errorCodes.add(PropertyErrorCodeEnum.PLATE_NUMBER_EXIST_ERR.getErrorCode());
            } else {
                propertyCarSaveDTO.setPlateNumber(plateNumber);
            }
        }

        // 处理地址
        String address = dataFormatter.formatCellValue(hssfRow.getCell(1));
        if (StringUtils.isBlank(address)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else {
            address = address.trim();
            String[] addresses = address.split("-");
            if (addresses.length != 3) {
                errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            } else {
//                PropertyProjectGetBuildInfoDTO propertyProjectGetBuildInfoDTO = new PropertyProjectGetBuildInfoDTO();
//                propertyProjectGetBuildInfoDTO.setProjectId(projectId);
//                propertyProjectGetBuildInfoDTO.setBuildName(addresses[0].trim());
//                PropertyProjectBuildInfoVO oneBuildInfo = iPropertyBuildInfoServiceClient.getOneBuildInfo(propertyProjectGetBuildInfoDTO);
//                if (oneBuildInfo == null) {
//                    errorCodes.add(PropertyErrorCodeEnum.PROJECT_BUILD_INFO_NOT_EXISTS_ERR.getErrorCode());
//                } else {
//                    PropertyProjectGetUnitInfoDTO propertyProjectGetUnitInfoDTO = new PropertyProjectGetUnitInfoDTO();
//                    propertyProjectGetUnitInfoDTO.setProjectId(projectId);
//                    propertyProjectGetUnitInfoDTO.setBuildId(oneBuildInfo.getBuildId());
//                    propertyProjectGetUnitInfoDTO.setUnitName(addresses[1].trim());
//                    PropertyProjectUnitInfoVO oneUnitInfo = iPropertyUnitInfoServiceClient.getOneUnitInfo(propertyProjectGetUnitInfoDTO);
//                    if (oneUnitInfo == null) {
//                        errorCodes.add(PropertyErrorCodeEnum.PROJECT_UNIT_INFO_NOT_EXISTS_ERR.getErrorCode());
//                    } else {
//                        PropertyProjectGetRoomInfoDTO propertyProjectGetRoomInfoDTO = new PropertyProjectGetRoomInfoDTO();
//                        propertyProjectGetRoomInfoDTO.setProjectId(projectId);
//                        propertyProjectGetRoomInfoDTO.setBuildId(oneBuildInfo.getBuildId());
//                        propertyProjectGetRoomInfoDTO.setUnitId(oneUnitInfo.getUnitId());
//                        propertyProjectGetRoomInfoDTO.setRoomNum(StringHandlerUtil.formatStrLen(addresses[2].trim(),"0",4,false,false));
//                        PropertyProjectRoomInfoVO oneRoomInfo = iPropertyRoomInfoServiceClient.getOneRoomInfo(propertyProjectGetRoomInfoDTO);
//                        if (oneRoomInfo == null) {
//                            errorCodes.add(PropertyErrorCodeEnum.PROJECT_ROOM_INFO_NOT_EXISTS_ERR.getErrorCode());
//                        } else {
//                            propertyCarSaveDTO.setBuildId(oneRoomInfo.getBuildId());
//                            propertyCarSaveDTO.setUnitId(oneRoomInfo.getUnitId());
//                            propertyCarSaveDTO.setRoomId(oneRoomInfo.getRoomId());
//                        }
//                    }
//                }
                boolean flag = true;
                String roomNum = StringHandlerUtil.formatStrLen(addresses[2].trim(), "0", 4, false, false);
                for (PropertyProjectRoomInfoVO roomInfoVO : roomInfoFromDB){
                    if (roomInfoVO.getBuildName().equals(addresses[0]) && roomInfoVO.getUnitName().equals(addresses[1]) && roomInfoVO.getRoomNum().equals(roomNum)) {
                        propertyCarSaveDTO.setBuildId(roomInfoVO.getBuildId());
                        propertyCarSaveDTO.setUnitId(roomInfoVO.getUnitId());
                        propertyCarSaveDTO.setRoomId(roomInfoVO.getRoomId());
                        flag = false;
                        break;
                    }
                }
                if (flag) {
                    errorCodes.add(PropertyErrorCodeEnum.PROJECT_ROOM_INFO_NOT_EXISTS_ERR.getErrorCode());
                }
            }
        }
        propertyCarRowDTO.setAddress(address);

        // 处理车位号
        String parkingNum = dataFormatter.formatCellValue(hssfRow.getCell(2));
        if (StringUtils.isNotBlank(parkingNum)) {
            parkingNum = parkingNum.trim();
            List<String> parkingNums = StringHandlerUtil.splitStringList(parkingNum);
            if (parkingNums.size() > 6) {
                errorCodes.add(PropertyErrorCodeEnum.CAR_BINDING_PARKING_ERR.getErrorCode());
            }
//            PropertyParkingGetOneDTO propertyParkingGetOneDTO = new PropertyParkingGetOneDTO();
//            propertyParkingGetOneDTO.setProjectId(projectId);
            String parkingId = "";
            boolean flag = true;
            for (String parkNum : parkingNums){
//                propertyParkingGetOneDTO.setParkingNum(parkNum);
//                PropertyParkingGetVO oneParking = iPropertyParkingServiceClient.getOneParking(propertyParkingGetOneDTO);
//                if (oneParking == null) {
//                    errorCodes.add(PropertyErrorCodeEnum.PARKING_NUM_NOT_EXIST_ERR.getErrorCode());
//                    break;
//                } else {
//                    if (StringUtils.isBlank(parkingId)) {
//                        parkingId = oneParking.getParkingId();
//                    } else {
//                        parkingId += ("," + oneParking.getParkingId());
//                    }
//                }
                for (PropertyParkingGetVO propertyParkingGetVO : parkingFromDB){
                    if (propertyParkingGetVO.getParkingNum().equals(parkNum)) {
                        if (StringUtils.isBlank(parkingId)) {
                            parkingId = propertyParkingGetVO.getParkingId();
                        } else {
                            parkingId += ("," + propertyParkingGetVO.getParkingId());
                        }
                        flag = false;
                        break;
                    }
                }
                if (flag) {
                    errorCodes.add(PropertyErrorCodeEnum.PARKING_NUM_NOT_EXIST_ERR.getErrorCode());
                    break;
                }
                flag = true;
            }
            propertyCarSaveDTO.setParkingId(parkingId);
        }
        propertyCarRowDTO.setParkingNum(parkingNum);

        // 处理车主姓名
        String ownerName = dataFormatter.formatCellValue(hssfRow.getCell(3));
        if (StringUtils.isBlank(ownerName)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else {
            ownerName = ownerName.trim();
            propertyCarSaveDTO.setOwnerName(ownerName);
        }
        propertyCarRowDTO.setOwnerName(ownerName);

        // 处理车主联系方式
        String ownerPhone = dataFormatter.formatCellValue(hssfRow.getCell(4));
        if (StringUtils.isBlank(ownerPhone)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else {
            ownerPhone = ownerPhone.trim();
            String regex = "^1[3|4|5|6|7|8|9]\\d{9}$";
            if (ownerPhone.length() != 11 || !Pattern.compile(regex).matcher(ownerPhone).matches()) {
                errorCodes.add(PropertyErrorCodeEnum.APPUSER_PHONENUM_ERR.getErrorCode());
            }
            propertyCarSaveDTO.setOwnerPhone(ownerPhone);
        }
        propertyCarRowDTO.setOwnerPhone(ownerPhone);

        // 处理机动车类型
        String carType = dataFormatter.formatCellValue(hssfRow.getCell(5));
        if (StringUtils.isNotBlank(carType)) {
//            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
//        } else {
            carType = carType.trim();
            int i = StringHandlerUtil.strToIntDef(carType, -1);
            if (NumberHandlerUtil.isInRange(i,1,4)) {
                propertyCarSaveDTO.setCarType(i);
            } else {
                errorCodes.add(PropertyErrorCodeEnum.CAR_TYPE_AVAILABLE_ERR.getErrorCode());
            }
        }
        propertyCarRowDTO.setCarType(carType);

        propertyCarSaveDTOList.add(propertyCarSaveDTO);
        return propertyCarRowDTO;
    }

    /**
     * <desc>
     *     向错误列表中增加有错误的车位信息.
     * </desc>
     *
     * @param  errorImportDataList 错误列表
     * @param  propertyCarRowDTO 有错误的车辆信息
     * @author wangzhi
     * @createDate 2018/08/23
     */
    private void addErrorCarInfoToList(List<PropertyCarRowDTO> errorImportDataList, PropertyCarRowDTO propertyCarRowDTO) {
        for (PropertyCarRowDTO oneAddressInfo:errorImportDataList){
            if ((oneAddressInfo.getRowNum() ==  propertyCarRowDTO.getRowNum())
                    && (StringHandlerUtil.compareStrWithOutEmpty(oneAddressInfo.getSheetName(),propertyCarRowDTO.getSheetName()))){
                return;
            }
        }
        errorImportDataList.add(propertyCarRowDTO);
    }

    /**
     * <desc>
     *      对导入文件的文件格式等基本信息进行合法性初步检测
     * </desc>
     *
     * @param workbook Excel操作对象
     * @param availableSheets 合法可用到Sheet页
     * @return MessageVO 标准格式化响应结果
     * @author wangzhi
     * @createDate 2018/08/23
     */
    private MessageVO checkImportFileValid(HSSFWorkbook workbook, List<HSSFSheet> availableSheets) {
        //获取Excel的Sheet页面数，每个Excel文件至少会有一个Sheet页(Excel文件本身到限定)
        int sheetCount = workbook.getNumberOfSheets();
        //遍历当前文件的所有Sheet页，进行合法性检测
        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
            HSSFSheet oneSheet = workbook.getSheetAt(sheetIndex);
            int rowsCountOfCurrentSheet = oneSheet.getPhysicalNumberOfRows();
            if (rowsCountOfCurrentSheet < 1) {
                continue;
            }
            //对当前Sheet的列数量进行合法性判断
            HSSFRow headerRow = oneSheet.getRow(0);
            int columnCount = oneSheet.getRow(0).getPhysicalNumberOfCells();
            if (columnCount != PlatformConstants.CAR_EXCEL_COLUMN_TITLES.length) {
                return new MessageVO(PropertyErrorCodeEnum.CAR_EXCEL_COLUMN_COUNT_MISMATCH_ERR.getErrorCode());
            }
            //每个Sheet页的第一页为列标题，进行标题头名称进行合法性判断
            DataFormatter dataFormatter = new DataFormatter();
            for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
                String oneFieldValue = dataFormatter.formatCellValue(headerRow.getCell(columnIndex));

                String columnTitle = PlatformConstants.CAR_EXCEL_COLUMN_TITLES[columnIndex];
                if (!StringHandlerUtil.compareStrWithOutEmpty(oneFieldValue, columnTitle)) {
                    ErrorCode errorCode = PropertyErrorCodeEnum.CAR_EXCEL_COLUMN_TITLE_MISMATCH_ERR.getErrorCode();
                    return new MessageVO(errorCode,
                            String.format(errorCode.getCodeMsg(), oneFieldValue, columnTitle));
                }
            }
            //至少有一行实际数据可导入才加入Sheet处理列表
            if (rowsCountOfCurrentSheet > 1) {
                availableSheets.add(oneSheet);
            }
        }
        //没有数据
        if (availableSheets.isEmpty()) {
            return new MessageVO(PropertyErrorCodeEnum.PROJECT_IMPORT_FILE_NO_DATA_ERR.getErrorCode());
        }
        return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode());
    }

    /**
     * <desc>
     *      上传车辆相关图片
     * </desc>
     *
     * @param request HTTP协议对象
     * @param propertyCarUploadDTO 车辆信息上传图片DTO
     * @return 标准格式化响应结果
     * @author wangzhi
     * @createDate 2018/08/22
     */
    private MessageVO uploadCarImg(HttpServletRequest request, PropertyCarUploadDTO propertyCarUploadDTO) throws Exception {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String,String> imageMap = new HashMap<>();
        for (int i = 1; i <= 3; i++) {
            List<MultipartFile> fileList = multipartRequest.getFiles("file" + i);
            Iterator<MultipartFile> fileIterator = fileList.iterator();
            while (fileIterator.hasNext()){
                MultipartFile file = fileIterator.next();
                String filename = file.getOriginalFilename();
                InputStream fileStream = file.getInputStream();
                ByteArrayOutputStream streamForClone = this.cloneInputStream(fileStream);
                String fileUrl;
                try {
                    InputStream upLoadStream = new ByteArrayInputStream(streamForClone.toByteArray());
                    try {
                        fileUrl = this.uploadFile(upLoadStream, filename);
                    }finally {
                        upLoadStream.close();
                    }
                } finally {
                    streamForClone.close();
                }
                if (StringUtils.isBlank(fileUrl)) {
                    return new MessageVO(PropertyErrorCodeEnum.UPLOAD_IMAGE_ERR.getErrorCode());
                }
                String url = imageMap.get("image" + i);
                if (StringUtils.isBlank(url)) {
                    imageMap.put("image" + i,fileUrl);
                } else {
                    url += ("," + fileUrl);
                    imageMap.put("image" + i,url);
                }
            }
        }
        propertyCarUploadDTO.setCarPicture(imageMap.get("image1"));
        propertyCarUploadDTO.setCarLicence(imageMap.get("image2"));
        propertyCarUploadDTO.setOwnerLicence(imageMap.get("image3"));
        return null;
    }

    /**
     * <desc>
     *      拷贝流
     * </desc>
     *
     * @param inputStream 输入流
     * @return 拷贝后的输出流
     * @author wangzhi
     * @createDate 2018/08/22
     */
    private ByteArrayOutputStream cloneInputStream(InputStream inputStream) throws IOException {
        ByteArrayOutputStream forCloneStream = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int readLength = 0;
        while ((readLength = inputStream.read(buffer)) != -1) {
            forCloneStream.write(buffer, 0, readLength);
        }
        forCloneStream.flush();
        return forCloneStream;
    }

    /**
     * <desc>
     *      分页获取车辆信息
     * </desc>
     *
     * @param propertyCarGetPageDTO 查询参数
     * @return 标准格式化响应结果
     * @author wangzhi
     * @createDate 2018/08/22
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping(path = "getCarsByPage")
    public Object getCarsByPage(PropertyCarGetPageDTO propertyCarGetPageDTO){
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            propertyCarGetPageDTO = HttpProtocolUtil.parseRequestParamToDTO(PropertyCarGetPageDTO.class, request);
            if (propertyCarGetPageDTO == null) {
                return new MessageVO(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
            if (StringUtils.isBlank(propertyCarGetPageDTO.getProjectId())) {
                return new MessageVO(PropertyErrorCodeEnum.PUB_PROJECT_ID_EMPTY_ERR.getErrorCode());
            }
            Page<PropertyCarGetPageVO> propertyCarGetPageVOS = iPropertyCarServiceClient.getCarsByPage(propertyCarGetPageDTO);
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(),propertyCarGetPageVOS);
        } catch (Exception e) {
            throw new DataAccessException("【物业-物业管理】分页获取车辆信息失败",e);
        }
    }

    /**
     * <desc>
     *      获取单个车辆信息
     * </desc>
     *
     * @param propertyCarGetOneDTO 获取单个车辆信息查询参数DTO
     * @return 标准格式化响应结果
     * @author wangzhi
     * @createDate 2018/08/22
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping(path = "getOneCar")
    public Object getOneCar(PropertyCarGetOneDTO propertyCarGetOneDTO){
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            propertyCarGetOneDTO = HttpProtocolUtil.parseRequestParamToDTO(PropertyCarGetOneDTO.class, request);
            if (propertyCarGetOneDTO == null) {
                return new MessageVO(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
            if (StringUtils.isBlank(propertyCarGetOneDTO.getProjectId())) {
                return new MessageVO(PropertyErrorCodeEnum.PUB_PROJECT_ID_EMPTY_ERR.getErrorCode());
            }
            if (StringUtils.isBlank(propertyCarGetOneDTO.getCarId()) && StringUtils.isBlank(propertyCarGetOneDTO.getPlateNumber())) {
                return new MessageVO(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
            PropertyCarGetOneVO propertyCarGetOneVO = iPropertyCarServiceClient.getOneCar(propertyCarGetOneDTO);
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(),propertyCarGetOneVO);
        } catch (Exception e) {
            throw new DataAccessException("【物业-物业管理】获取单个车辆信息失败",e);
        }
    }

    /**
     * <desc>
     *      Ftp上传文件，返回Url.
     * </desc>
     *
     * @param inputStream 文件流
     * @param fileName 文件名称
     * @return 文件上传成功后会返回Url,否则返回空
     * @author wangzhi
     * @createDate 2018/08/22
     */
    private String uploadFile(InputStream inputStream, String fileName) throws IOException {
        String fileUrl = "";
        String fileType = StringHandlerUtil.copySubStr(fileName, fileName.lastIndexOf('.') + 1, fileName.length());
        UploadFileUtil.UploadFile uploadFileUtil = new UploadFileUtil().new UploadFile(
                FILE_FTP_HOST,
                FILE_FTP_USERNAME,
                FILE_FTP_PASSWORD,
                FILE_FTP_PORT,
                inputStream,
                fileType,
                FILE_ACCESS_URL,
                FileCatalogEnum.PROPERTY_PARKING_AND_CAR_FILE.getType()
        );
        Map<String, Object> resultMap = UploadFileUtil.uploadFile(uploadFileUtil);
        Boolean returnCode = (Boolean)resultMap.get("returnCode");
        if (returnCode == false){
            return fileUrl;
        }
        fileUrl = resultMap.get("accessPath").toString();
        return fileUrl;
    }

}
